package com.apes.fn.crm.customerExpand;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface FnCustomerExpandRepository extends CustomRepository<FnCustomerExpand, String> {
    List<FnCustomerExpand> findAllByMobileOrCarLicenseOrRealname(String value1,String value2,String value3);
    Page<FnCustomerExpand> findAllByMobileOrCarLicenseOrRealnameAndVendorCode(String value1,String value2,String value3, String value4,  Pageable pageable);


    @Query(value = "select a from FnCustomerExpand a where a.carLicense = :carLicense order by a.createDate desc")
    List<FnCustomerExpand> findAllByCarLicense(String carLicense);
    @Query(value = "select *" +
            " from (select CAR_LICENSE AS ID, max(realname) AS NAME" +
            "       from (select a.car_license, a.realname realname" +
            "             from yyesb.fn_customer_expand a" +
            "             where a.car_license like %:carLicense%" +
            "             and MOBILE is not null " +
            "             order by a.CREATE_DATE desc)" +
            "       group by CAR_LICENSE)" +
            " where ROWNUM < :maxsize" +
            "   and rownum > :minsize",nativeQuery = true)
    List<Map> findCarLicense(String carLicense,Integer maxsize,Integer minsize);

    @Query(value = "select a from FnCustomerExpand a where a.carLicense = :carLicense and a.vendorCode = :vendorCode order by a.createDate desc")
    List<FnCustomerExpand> findAllByCarLicenseAndVendorCode(String carLicense, String vendorCode);
}
